package com.zwh.tool.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * excel 工具类
 *
 * @author 别人
 */
public class ExcelUtils {

    /**
     * 提取一个 excel 中的所有单元格内容
     *
     * @return 最外层List是sheet，内层为每行，Map<String, String>为每格，key为第几列(从1开始)，value为内容
     */
    public static List<List<Map<String, String>>> extractExcelAll(InputStream inputStream, String extension) throws IOException {
        List<List<Map<String, String>>> sheetList = new ArrayList<>();
        Workbook workbook = getWorkbook(inputStream, extension);
        for (Sheet sheet : workbook) {
            List<Map<String, String>> rowList = new ArrayList<>();
            for (Row row : sheet) {
                Map<String, String> map = new HashMap<>();
                int i = 1;
                for (Cell cell : row) {
                    String val = getCellValue(cell);
                    map.put(i + "", val);
                    i++;
                }
                rowList.add(map);
            }
            sheetList.add(rowList);
        }
        return sheetList;
    }

    public static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日 HH时mm分ss秒";//默认日期格式
    public static final int DEFAULT_COLUMN_WIDTH = 17; // 默认最少宽度(单位:字节数)

    /**
     * 导出 Excel 表格格式
     *
     * @param title       标题行
     * @param headMap     表头。key: 字段名(用于映射对应数据对象的值)，value: 表头名(导出后的表头名称)。
     * @param dataList    表格内容数据集
     * @param datePattern 日期格式，null则用默认日期格式。
     * @param minColWidth 最小列宽，默认值为 DEFAULT_COLUMN_WIDTH。
     * @param out         输出流
     * @param extension   文件扩展名
     */
    public static void exportExcel(String title, Map<String, String> headMap, List<Object> dataList,
                                   String datePattern, int minColWidth, OutputStream out, String extension) throws IOException {
        if (datePattern == null) {
            datePattern = DEFAULT_DATE_PATTERN;
        }
        // 声明一个工作薄
        Workbook workbook = getWorkbook(extension);
        // 标题样式
        CellStyle titleStyle = getDefaultTitleStyle(workbook);
        // 表头样式
        CellStyle headStyle = getDefaultHeadStyle(workbook);
        // 表格内容单元格样式
        CellStyle cellStyle = getDefaultContentStyle(workbook);

        Sheet sheet = workbook.createSheet();
        // 定义最小列宽
        int minWidth = Math.max(minColWidth, DEFAULT_COLUMN_WIDTH);

        int[] arrColWidth = new int[headMap.size()];
        // 生成产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int columnIndex = 0;

        for (Map.Entry<String, String> entry : headMap.entrySet()) {
            String fieldName = entry.getKey();

            properties[columnIndex] = fieldName;
            headers[columnIndex] = entry.getValue();

            int bytes = fieldName.getBytes().length;
            arrColWidth[columnIndex] = Math.max(bytes, minWidth);
            sheet.setColumnWidth(columnIndex, arrColWidth[columnIndex] * 256);
            columnIndex++;
        }

        // 遍历集合数据，产生数据行
        int rowIndex = 0;

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
        // 创建标题行并合并单元格
        Row titleRow = sheet.createRow(0);//表头 rowIndex=0
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(title);
        titleCell.setCellStyle(titleStyle);

        // 创建表头
        Row headRow = sheet.createRow(1); //列头 rowIndex =1
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headStyle);
        }

        rowIndex = 2;//数据内容从 rowIndex=2开始

        for (Object obj : dataList) {
            if (rowIndex == 65535) {
                sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
            }
            JSONObject jo = (JSONObject) JSON.toJSON(obj);
            Row dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                Cell newCell = dataRow.createCell(i);

                Object o = jo.get(properties[i]);

                String cellValue = "";
                if (o == null) {
                    cellValue = "";
                } else if (o instanceof Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else {
                    cellValue = o.toString();
                }

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        write(workbook, out);
    }

    /**
     * 默认表格内容样式
     */
    private static CellStyle getDefaultContentStyle(Workbook workbook) {
        CellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框样式
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        Font cellFont = workbook.createFont();
        contentStyle.setFont(cellFont);
        return contentStyle;
    }

    /**
     * 默认表头样式
     */
    private static CellStyle getDefaultHeadStyle(Workbook workbook) {
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        Font headFont = workbook.createFont();
        headFont.setColor(Font.COLOR_RED);
        headFont.setFontHeightInPoints((short) 12);
        headStyle.setFont(headFont);
        return headStyle;
    }

    /**
     * 默认表头样式
     */
    private static CellStyle getDefaultTitleStyle(Workbook workbook) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        return titleStyle;
    }

    /**
     * 3.x版本时，要返回 CellType 必须调用 cell.getCellTypeEnum()。
     * 4.x版本时，要返回 CellType 须调用 cell.getCellType() —— 部分版本保留了getCellTypeEnum()这个方法，但是终究会被删除，所以换掉最好。
     * 为了避免复制此工具类时修改过多，直接提取出来
     */
    public static CellType getCellType(Cell cell) {
        // 3.x版本用
        return cell.getCellTypeEnum();
        // 4.x版本用
        // return cell.getCellType();
    }

    /**
     * 复制行。
     *
     * @param srcRow  源行。
     * @param destRow 目标行。
     */
    public static void copyRow(Row srcRow, Row destRow, CellCopyPolicy policy) {
        if (policy.isCopyRowHeight()) {
            destRow.setHeight(srcRow.getHeight());
        }
        for (Iterator<Cell> cellIt = srcRow.cellIterator(); cellIt.hasNext(); ) {
            Cell srcCell = cellIt.next();
            Cell destCell = destRow.createCell(srcCell.getColumnIndex(), getCellType(srcCell));
            copyCell(srcCell, destCell, policy);
        }
    }

    /**
     * 复制单元格
     * 修改自 poi:5.2.2
     *
     * @param srcCell  源Cell
     * @param destCell 目标Cell
     * @param policy   复制方案
     */
    public static void copyCell(Cell srcCell, Cell destCell, CellCopyPolicy policy) {

        if (srcCell != null) {

            if (policy.isCopyCellValue()) {
                CellType copyCellType = getCellType(srcCell);
                if (copyCellType == CellType.FORMULA && !policy.isCopyCellFormula()) {
                    // 同 getCellType() 一样，该 Enum() 方法在新版本也需要更换
                    copyCellType = srcCell.getCachedFormulaResultTypeEnum();
                }
                switch (copyCellType) {
                    case NUMERIC:
                        // DataFormat is not copied unless policy.isCopyCellStyle is true
                        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(srcCell)) {
                            destCell.setCellValue(srcCell.getDateCellValue());
                        } else {
                            destCell.setCellValue(srcCell.getNumericCellValue());
                        }
                        break;
                    case STRING:
                        destCell.setCellValue(srcCell.getRichStringCellValue());
                        break;
                    case FORMULA:
                        destCell.setCellFormula(srcCell.getCellFormula());
                        break;
                    case BLANK:
                        destCell.setCellType(CellType.BLANK);
                        break;
                    case BOOLEAN:
                        destCell.setCellValue(srcCell.getBooleanCellValue());
                        break;
                    case ERROR:
                        destCell.setCellErrorValue(srcCell.getErrorCellValue());
                        break;
                    default:
                        throw new IllegalArgumentException("Invalid cell type " + copyCellType);
                }
            }

            if (policy.isCopyCellStyle()) {
                if (srcCell.getSheet() != null && destCell.getSheet() != null &&
                        destCell.getSheet().getWorkbook() == srcCell.getSheet().getWorkbook()) {
                    destCell.setCellStyle(srcCell.getCellStyle());
                } else {
                    CellStyle srcStyle = srcCell.getCellStyle();
                    CellStyle destStyle;
                    destStyle = destCell.getSheet().getWorkbook().createCellStyle();
                    destStyle.cloneStyleFrom(srcStyle);
                    destCell.setCellStyle(destStyle);
                }
            }

            //批注
            if (srcCell.getCellComment() != null) {
                destCell.setCellComment(srcCell.getCellComment());
            }
        }

    }

    /**
     * 根据扩展名获取对应的Workbook
     *
     * @param extension 扩展名
     */
    public static Workbook getWorkbook(String extension) throws IOException {
        return getWorkbook(null, extension);
    }

    /**
     * 根据扩展名获取对应的Workbook
     *
     * @param inputStream 输入流，原excel文件。如果不为null，则以该输入流的对应excel文件构建对象。
     */
    public static Workbook getWorkbook(InputStream inputStream, String extension) throws IOException {
        Workbook workbook;
        if ("xlsx".equalsIgnoreCase(extension)) {
            workbook = inputStream == null ? new XSSFWorkbook() : new XSSFWorkbook(inputStream);
        } else if ("xls".equalsIgnoreCase(extension)) {
            workbook = inputStream == null ? new HSSFWorkbook() : new HSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("Unsupported file extension: " + extension);
        }
        return workbook;
    }

    /**
     * 写出成文件
     *
     * @param filePath 文件路径+文件名
     */
    public static void write(Workbook workbook, String filePath) throws IOException {
        try (OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(filePath))) {
            write(workbook, outputStream);
        }
    }

    public static void write(Workbook workbook, OutputStream outputStream) throws IOException {
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            outputStream.flush();
            outputStream.close();
            workbook.close();
        }
    }

    /**
     * 获取单元格中存储的值。可借由实际业务修改实现。
     */
    public static String getCellValue(Cell cell) {
        String cellValue = null;
        if (cell == null) {
            return cellValue;
        }
        CellType cellType = getCellType(cell);
        // 根据实际项目自由调整
        switch (cellType) {
            case BLANK:
                // BLANK时，cell.getStringCellValue()默认就返回空字符串
                cellValue = "";
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                // TIPS: Excel中没有DATE这种类型，底层存储的是一串特殊格式化后的数字，所以如果为数字类型，则需要判断一下是否为日期
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) || isCustomDateFormatted(cell)) {
                    Date dateCellValue = cell.getDateCellValue();
                    // 格式化根据实际项目优化
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(dateCellValue);
                } else {
                    double numericValue = cell.getNumericCellValue();
                    DecimalFormat decimalFormat = new DecimalFormat("#.#####");
                    cellValue = decimalFormat.format(numericValue);
                }
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                cellValue = FormulaError.forInt(cell.getErrorCellValue()).getString();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
            default:
        }
        return cellValue;
    }

    /**
     * 检查是否为自定义的日期数据格式
     *
     * @see DateUtil#isADateFormat(int, String) 底层方法只会判断斜杠以及短横线进行替换，并不适配自定义的带汉字的数据格式，所以需要自己实现一个检查(可以有别的方案，这里用简单的方案实现)
     */
    private static boolean isCustomDateFormatted(Cell cell) {
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        return "yyyy年MM月dd日 HH时mm分ss秒".equals(dataFormatString) || "yyyy年MM月dd日".equals(dataFormatString);
    }

}
